package com.pub.tools;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;

/**
 * Created by bscj on 2017/7/20.
 */
public class POITools {


    /**
     * 设置excel单元格格式 ,参数含义：（HSSFWorkbook wb,对齐方式，边框类型，字体类型，字体大小，是否加粗，是否自动换行）
     * 参数详细情况在下边，以下还有一些设置单元格属性方法，供参考：
     * (下划线：UnderlineStyle.NO_UNDERLINE，字体颜色：HSSFColor.YELLOW.index
     * 背景颜色：style.setFillForegroundColor(HSSFColor.SKY_BLUE.index)
     * HSSFCellStyle.
     * setFillPattern(HSSFCellStyle.SOLID_FOREGROUND)，代码中只实现了常用的一些属性)
     *
     * @param Alignment
     *            String c：居中，l：左对齐 ，r:右对齐 （大小写均可）
     * @param Border
     *            String thin:细线，thick:粗线，no或''表示不要边框
     * @param Font
     *            String 宋体，ARIAL，TIMES
     * @param fontSize
     *            int 字体大小
     * @param fontBold
     *            BOLD:加粗， NO_BOLD：不加粗
     * @param ifWrap
     *            boolean yes:自动换行，no:不换行
     * @return jxl.write.WritableCellFormat
     */
    public static org.apache.poi.hssf.usermodel.HSSFCellStyle poiSetExcelCellStyle(
            org.apache.poi.hssf.usermodel.HSSFWorkbook wb, String Alignment,
            String Border, String Font, int fontSize, String fontBold,
            boolean ifWrap) {
        org.apache.poi.hssf.usermodel.HSSFCellStyle cellStyle = wb
                .createCellStyle();
        // 生成一个字体
        org.apache.poi.hssf.usermodel.HSSFFont font = wb.createFont();
        // font.setColor(HSSFColor.BLACK.index);//HSSFColor.VIOLET.index //字体颜色
        // 是否加粗
        if (fontBold.toUpperCase().equals("BOLD")) {
            font.setBoldweight(org.apache.poi.hssf.usermodel.HSSFFont.BOLDWEIGHT_BOLD);
        } else {
            font.setBoldweight(org.apache.poi.hssf.usermodel.HSSFFont.BOLDWEIGHT_NORMAL);
        }
        // 字体类型
        if (Font.equals("")) {
            Font = "ARIAL";
        }
        font.setFontName(Font);
        // 字体大小
        font.setFontHeightInPoints((short) fontSize);
        cellStyle.setFont(font);// 把字体应用到当前的样式
        // 自动换行
        cellStyle.setWrapText(true);// 设置自动换行
//        cellStyle.setShrinkToFit(ifWrap);// 自适应宽度
        // 默认垂直居中对齐
        cellStyle
                .setVerticalAlignment(org.apache.poi.hssf.usermodel.HSSFCellStyle.VERTICAL_CENTER);// 垂直
        cellStyle
                .setAlignment(org.apache.poi.hssf.usermodel.HSSFCellStyle.ALIGN_CENTER); // 居中
        if (Alignment.toLowerCase().equals("l")) {
            cellStyle
                    .setAlignment(org.apache.poi.hssf.usermodel.HSSFCellStyle.ALIGN_LEFT);
        } else if (Alignment.toLowerCase().equals("r")) {
            cellStyle
                    .setAlignment(org.apache.poi.hssf.usermodel.HSSFCellStyle.ALIGN_RIGHT);
        }
        // 边框
        if (Border.toLowerCase().equals("thin")) {
            cellStyle
                    .setBorderBottom(org.apache.poi.hssf.usermodel.HSSFCellStyle.BORDER_THIN); // 下边框
            cellStyle
                    .setBorderLeft(org.apache.poi.hssf.usermodel.HSSFCellStyle.BORDER_THIN);// 左边框
            cellStyle
                    .setBorderTop(org.apache.poi.hssf.usermodel.HSSFCellStyle.BORDER_THIN);// 上边框
            cellStyle
                    .setBorderRight(org.apache.poi.hssf.usermodel.HSSFCellStyle.BORDER_THIN);// 右边框
        } else if (Border.toLowerCase().equals("thick")) {
            cellStyle
                    .setBorderBottom(org.apache.poi.hssf.usermodel.HSSFCellStyle.BORDER_THICK); // 下边框
            cellStyle
                    .setBorderLeft(org.apache.poi.hssf.usermodel.HSSFCellStyle.BORDER_THICK);// 左边框
            cellStyle
                    .setBorderTop(org.apache.poi.hssf.usermodel.HSSFCellStyle.BORDER_THICK);// 上边框
            cellStyle
                    .setBorderRight(org.apache.poi.hssf.usermodel.HSSFCellStyle.BORDER_THICK);// 右边框
        }
        return cellStyle;
    }

    /**
     * 根据行内容重新计算行高
     *
     * @param sourceRow
     */
    public static void calcAndSetRowHeigt(HSSFRow sourceRow) {
        // 原行高
        short height = sourceRow.getHeight();
        // 计算后的行高
        double maxHeight = height;
        for (int cellIndex = sourceRow.getFirstCellNum(); cellIndex <= sourceRow
                .getPhysicalNumberOfCells(); cellIndex++) {
            HSSFCell sourceCell = sourceRow.getCell(cellIndex);
            // 单元格的内容
            String cellContent = getCellContentAsString(sourceCell);
            if (null == cellContent || "".equals(cellContent)) {
                continue;
            }
            // 单元格的宽度
            int columnWidth = getCellWidth(sourceCell);
            HSSFCellStyle cellStyle = sourceCell.getCellStyle();
            HSSFFont font = cellStyle.getFont(sourceRow.getSheet()
                    .getWorkbook());
            // 字体的高度
            short fontHeight = font.getFontHeight();
            // cell内容字符串总宽度
            double cellContentWidth = cellContent.getBytes().length * 2 * 256;// 256;
            // 字符串需要的行数 不做四舍五入之类的操作
            double stringNeedsRows = (double) cellContentWidth / columnWidth;
            // 小于一行补足一行
            if (stringNeedsRows < 1.0) {
                stringNeedsRows = 1.0;
            } else {
                // 因为行间距问题，每隔5行去一行
                stringNeedsRows -= (int) stringNeedsRows / 5;
            }
            // 需要的高度 (Math.floor(stringNeedsRows) - 1) * 40 为两行之间空白高度
            double stringNeedsHeight = (double) fontHeight * stringNeedsRows;
            if (stringNeedsHeight > maxHeight) {
                maxHeight = stringNeedsHeight;
            }
        }
        // 最后取天花板防止高度不够
        maxHeight = Math.ceil(maxHeight);
        sourceRow.setHeight((short) maxHeight);
    }

    /**
     * 解析一个单元格得到数据
     *
     * @return
     */
    public static String getCellContentAsString(HSSFCell cell) {
        if (null == cell) {
            return "";
        }
        String result = "";
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:
                String s = String.valueOf(cell.getNumericCellValue());
                if (s != null) {
                    if (s.endsWith(".0")) {
                        s = s.substring(0, s.length() - 2);
                    }
                }
                result = s;
                break;
            case Cell.CELL_TYPE_STRING:
                if (cell.getStringCellValue() != null) {
                    result = String.valueOf(cell.getStringCellValue()).trim();
                }
                break;
            case Cell.CELL_TYPE_BLANK:
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                result = String.valueOf(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_ERROR:
                break;
            default:
                break;
        }
        return result;
    }

    /**
     * 获取单元格及合并单元格的宽度
     *
     * @return int
     */
    private static int getCellWidth(HSSFCell cell) {
        int result = 0;
        HSSFSheet sheet = cell.getSheet();
        int rowIndex = cell.getRowIndex();
        int columnIndex = cell.getColumnIndex();

        boolean isPartOfRegion = false;
        int firstColumn = 0;
        int lastColumn = 0;
        int firstRow = 0;
        int lastRow = 0;
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            org.apache.poi.ss.util.CellRangeAddress ca = sheet
                    .getMergedRegion(i);
            firstColumn = ca.getFirstColumn();
            lastColumn = ca.getLastColumn();
            firstRow = ca.getFirstRow();
            lastRow = ca.getLastRow();
            if (rowIndex >= firstRow && rowIndex <= lastRow) {
                if (columnIndex >= firstColumn && columnIndex <= lastColumn) {
                    isPartOfRegion = true;
                    break;
                }
            }
        }
        if (isPartOfRegion) {
            for (int i = firstColumn; i <= lastColumn; i++) {
                result += sheet.getColumnWidth(i);
            }
        } else {
            result = sheet.getColumnWidth(columnIndex);
        }
        return result;
    }


}
